1 Imports System.Data.SqlClient
2 Imports System.IO
3 Imports Excel = Microsoft.Office.Interop.Excel
4 Public Class frmStudentsCardRecord
5
6 Sub fillSession()
7 Try
8 con = New SqlConnection(cs)
9 con.Open()
10 adp = New SqlDataAdapter()
11 adp.SelectCommand = New SqlCommand("SELECT distinct (Session) FROM Student", con)
12 ds = New DataSet("ds")
13 adp.Fill(ds)
14 dtable = ds.Tables(0)
15 cmbSession.Items.Clear()
16 For Each drow As DataRow In dtable.Rows
17 cmbSession.Items.Add(drow(0).ToString())
18 Next
19 Catch ex As Exception
20 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
21 End Try
22 End Sub
23 Private Sub Button4_Click(sender As System.Object, e As System.EventArgs) Handles Button4.Click
24 Me.Close()
25 End Sub
26
27
28 Sub Reset()
29 cmbClass.SelectedIndex = -1
30 cmbSection.SelectedIndex = -1
31 cmbSession.SelectedIndex = -1
32 cmbClass.Enabled = False
33 cmbSection.Enabled = False
34 End Sub
35 Private Sub btnReset_Click(sender As System.Object, e As System.EventArgs) Handles btnReset.Click
36 Reset()
37 End Sub
38
39 Private Sub dgw_RowPostPaint(sender As Object, e As System.Windows.Forms.DataGridViewRowPostPaintEventArgs) Handles dgw.RowPostPaint
40 Dim strRowNumber As String = (e.RowIndex + 1).ToString()
41 Dim size As SizeF = e.Graphics.MeasureString(strRowNumber, Me.Font)
42 If dgw.RowHeadersWidth < Convert.ToInt32((size.Width + 20)) Then
43 dgw.RowHeadersWidth = Convert.ToInt32((size.Width + 20))
44 End If
45 Dim b As Brush = SystemBrushes.ControlText
46 e.Graphics.DrawString(strRowNumber, Me.Font, b, e.RowBounds.Location.X + 15, e.RowBounds.Location.Y + ((e.RowBounds.Height - size.Height) / 2))
47
48 End Sub
49
50 Private Sub Button1_Click(sender As System.Object, e As System.EventArgs) Handles Button1.Click
51 Dim rowsTotal, colsTotal As Short
52 Dim I, j, iC As Short
53 System.Windows.Forms.Cursor.Current = System.Windows.Forms.Cursors.WaitCursor
54 Dim xlApp As New Excel.Application
55 Try
56 Dim excelBook As Excel.Workbook = xlApp.Workbooks.Add
57 Dim excelWorksheet As Excel.Worksheet = CType(excelBook.Worksheets(1), Excel.Worksheet)
58 xlApp.Visible = True
59
60 rowsTotal = dgw.RowCount
61 colsTotal = dgw.Columns.Count - 1
62 With excelWorksheet
63 .Cells.Select()
64 .Cells.Delete()
65 For iC = 0 To colsTotal
66 .Cells(1, iC + 1).Value = dgw.Columns(iC).HeaderText
67 Next
68 For I = 0 To rowsTotal - 1
69 For j = 0 To colsTotal
70 .Cells(I + 2, j + 1).value = dgw.Rows(I).Cells(j).Value
71 Next j
72 Next I
73 .Rows("1:1").Font.FontStyle = "Bold"
74 .Rows("1:1").Font.Size = 12
75
76 .Cells.Columns.AutoFit()
77 .Cells.Select()
78 .Cells.EntireColumn.AutoFit()
79 .Cells(1, 1).Select()
80 End With
81 Catch ex As Exception
82 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
83 Finally
84 'RELEASE ALLOACTED RESOURCES
85 System.Windows.Forms.Cursor.Current = System.Windows.Forms.Cursors.Default
86 xlApp = Nothing
87 End Try
88 End Sub
89
90
91 Private Sub frmStaffCardRecord_Load(sender As System.Object, e As System.EventArgs) Handles MyBase.Load
92 fillSession()
93 End Sub
94
95 Private Sub cmbSession_SelectedIndexChanged(sender As System.Object, e As System.EventArgs) Handles cmbSession.SelectedIndexChanged
96 Try
97 cmbClass.Enabled = True
98 con = New SqlConnection(cs)
99 con.Open()
100 Dim ct As String = "SELECT distinct RTRIM(ClassName) FROM Student,Section,Class where Student.SectionID=Section.ID and Section.Class=Class.ClassName and Session=@d1"
101 cmd = New SqlCommand(ct)
102 cmd.Connection = con
103 cmd.Parameters.AddWithValue("@d1", cmbSession.Text)
104 rdr = cmd.ExecuteReader()
105 cmbClass.Items.Clear()
106 While rdr.Read
107 cmbClass.Items.Add(rdr(0))
108 End While
109 con.Close()
110 Catch ex As Exception
111 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
112 End Try
113 End Sub
114
115 Private Sub cmbClass_SelectedIndexChanged(sender As System.Object, e As System.EventArgs) Handles cmbClass.SelectedIndexChanged
116 Try
117 cmbSection.Enabled = True
118 con = New SqlConnection(cs)
119 con.Open()
120 Dim ct As String = "SELECT distinct RTRIM(SectionName) FROM Student,Section,Class where Student.SectionID=Section.ID and Section.Class=Class.ClassName and Session=@d1 and ClassName=@d2"
121 cmd = New SqlCommand(ct)
122 cmd.Connection = con
123 cmd.Parameters.AddWithValue("@d1", cmbSession.Text)
124 cmd.Parameters.AddWithValue("@d2", cmbClass.Text)
125 rdr = cmd.ExecuteReader()
126 cmbSection.Items.Clear()
127 While rdr.Read
128 cmbSection.Items.Add(rdr(0))
129 End While
130 con.Close()
131 Catch ex As Exception
132 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
133 End Try
134 End Sub
135
136 Private Sub btnSearch_Click(sender As System.Object, e As System.EventArgs) Handles btnSearch.Click
137 Try
138 If Len(Trim(cmbSession.Text)) = 0 Then
139 MessageBox.Show("Please select session", "", MessageBoxButtons.OK, MessageBoxIcon.Warning)
140 cmbSession.Focus()
141 Exit Sub
142 End If
143 If Len(Trim(cmbClass.Text)) = 0 Then
144 MessageBox.Show("Please select class", "", MessageBoxButtons.OK, MessageBoxIcon.Warning)
145 cmbClass.Focus()
146 Exit Sub
147 End If
148 If Len(Trim(cmbSection.Text)) = 0 Then
149 MessageBox.Show("Please select section", "", MessageBoxButtons.OK, MessageBoxIcon.Warning)
150 cmbSection.Focus()
151 Exit Sub
152 End If
153 con = New SqlConnection(cs)
154 con.Open()
155 cmd = New SqlCommand("select RTRIM(Student.AdmissionNo),RTRIM(StudentName),RTRIM(Cards_Student.Status) from Student,Class,Section,SchoolInfo,Cards_Student where Student.SectionID=Section.ID and Class.ClassName=Section.Class and SchoolInfo.S_ID=Student.SchoolID and Student.AdmissionNo=Cards_Student.AdmissionNo and Session=@d1 and ClassName=@d2 and SectionName=@d3 and Student.Status='Active' order by StudentName", con)
156 cmd.Parameters.AddWithValue("@d1", cmbSession.Text)
157 cmd.Parameters.AddWithValue("@d2", cmbClass.Text)
158 cmd.Parameters.AddWithValue("@d3", cmbSection.Text)
159 rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection)
160 dgw.Rows.Clear()
161 While (rdr.Read() = True)
162 dgw.Rows.Add(rdr(0), rdr(1), rdr(2))
163 End While
164 con.Close()
165 Catch ex As Exception
166 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
167 End Try
168 End Sub
169 End Class
